﻿CREATE PROCEDURE dbo.SP_MemberEducation_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @EducationId Int   =  NULL 
, @MemberId Int   =  NULL 
, @Name NVarChar (256)  =  NULL 
, @Type TinyInt   =  NULL 
, @Year Int   =  NULL 
	AS
			WITH CTE_MemberEducation AS
			( 
			SELECT
 							EducationId,
								MemberId,
								Name,
								Type,
								Year,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume='EducationId' THEN EducationId
												WHEN @OrderColume='MemberId' THEN MemberId
						 
						WHEN @OrderColume='Name' THEN Name
												WHEN @OrderColume='Type' THEN Type
												WHEN @OrderColume='Year' THEN Year
									ELSE 
							    EducationId
					        	END
			) AS "RowNumber"
  			FROM MemberEducation 
			where 
								(EducationId = @EducationId OR @EducationId Is Null)
										AND(MemberId = @MemberId OR @MemberId Is Null)
										AND(Name LIKE @Name OR @Name Is Null)
										AND(Type = @Type OR @Type Is Null)
										AND(Year = @Year OR @Year Is Null)
								)
			SELECT  *
			FROM CTE_MemberEducation
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			